
/*------------------------------------------------------------------------------

This code cleans and uses the survey data ("Enquete Revalo").

------------------------------------------------------------------------------*/



/*------------------------------------------------------------------------------

						CONSTRUCT SAMPLE

------------------------------------------------------------------------------*/

* regulatory filings

use matricule using "$output/DA", clear
bysort matricule: keep if _n==1

* survey data "enquete revalo"

merge 1:m matricule using "$source/bdd_revalo_all.dta"

* 16 insurers only in dossiers annuels (2% of provisions in enquete revalo)
* 36 insurers only in enquete revalo
* 61 insurers in both data (98% of provisions in enquete revalo)
preserve
bysort matricule: gen n = 1 if _n==1
collapse (sum) n PM, by(_m)
tabstat n PM, by(_m)
restore

keep if _m==3
drop _m

* drop if:
* missing variables
drop if taux_technique==. | nb_assures==.
* zero account value
drop if PM==0

* guaranteed rate
rename taux_technique rg
label var rg "Guaranteed rate"

* first commerzialisation date
rename annee_1ere_commercialisation year0
label var year0 "Year first commercialization"

* net contract return (taux_net = pb + taux_technique)
rename taux_net r
label var r "Net-of-fees return"

save "$output/ER", replace



*** SAMPLE FOR RETURN REGRESSIONS

* Bring year-start reserves from regulatory filings

use "$output/DA", clear
gen res0 = (Reserve_PPB_ig_lag + Reserve_RC_ig_lag + Reserve_unrealized_lag) / PM_ouv_ig if abs(alpha_ig-1)<.05
keep matricule year res0 PM_clo_ig
save "$output/temp", replace

/* Check quality of merge: Account value in survey data = 81% of account value in regulatory filings

use "$output/ER", clear
collapse (sum) PM_ER=PM, by(matricule year)
replace PM_ER = PM_ER*1e3 // convert in million euro
gen N_ER = 1
merge 1:1 matricule year using "$output/temp", keep(2 3) nogen
keep if year>=2011
gen N_DA = 1
collapse (sum) N_ER N_DA PM_ER PM_DA=PM_clo_ig, by(year)
gen frac_N_matched = N_ER/N_DA
gen frac_PM_matched = PM_ER/PM_DA
l year frac*
sum frac*

*/

use "$output/ER", clear

* bring in reserves in first year of commercialization

rename year currentyear
rename year0 year
merge m:1 matricule year using "$output/temp", keep(3) nogen
rename year year0
rename currentyear year

keep if res0<. & r<. & rg<.

* weights

egen totPM = sum(PM), by(year)
gen vw = PM/totPM
drop totPM

save "$output/ER_return", replace




/*------------------------------------------------------------------------------

						TABLE 1 PANEL C: SUMMARY STATS

------------------------------------------------------------------------------*/

use "$output/ER_return", clear

replace r = r*100
replace rg = rg*100
label var r "Net-of-fees return (\%)"
label var rg "Minimum guaranteed return (\%)"

eststo clear

estpost tabstat r rg [aw=vw], stat(mean sd p25 p50 p75 count) col(stat)

estout using "$results/sum-stat-er.tex", replace style(tex) cells("mean(fmt(%12.2g) label(Mean)) sd(fmt(%12.2g) label(S.D.)) p25(fmt(%12.2g) label(P25)) p50(fmt(%12.2g) label(P50)) p75(fmt(%12.2g) label(P75)) count(fmt(%12.0fc) label(N))") label mlabels(none) collabels(none) varwidth(50)

gen rg0 = rg==0
gen binding = r<=rg
gen binding_fee = r+.7<=rg
sum rg0 binding* [aw=vw]
sum rg0 binding* if open==1 [aw=vw]




/*------------------------------------------------------------------------------

						TABLE 4 COLUMN 3: NET-OF-FEES RETURNS

------------------------------------------------------------------------------*/

use "$output/ER_return", clear

* fixed effects

capture drop matyear
egen matyear = group(matricule year)
egen year0year = group(year0 year)

* Table 4 Column 3

label var res0 "Vintage-year reserves"

eststo clear

qui eststo: reghdfe r res0 [aw=vw], a(matyear year0year) vce(cluster matricule year)

estout using "$results/net-of-fee.tex", replace style(tex) label cells(b(fmt(%9.2g) star) se(fmt(%9.2g) par)) starlevels(* 0.1 ** 0.05 *** 0.01) varlabels(_cons Constant) mlabel(none) collabel(none) varwidth(40) drop(_cons)

estout using "$results/net-of-fee-r2.tex", replace style(tex) label stats(r2_a N, fmt(%8.2g %8.0fc) label("$ R^2$" "Observations")) drop(*) mlabel(none) collabel(none)




/*------------------------------------------------------------------------------

					TABLE 10: FINANCIAL SOPHISTICATION

------------------------------------------------------------------------------*/

*** reserves and portfolio shares from regulatory filings

use "$output/DA", clear
xtset matricule year

* asset return

gen Pa_ig = PM_ouv_ig + (Inflow_ig-Outflow_ig)/2
gen x = Retass_ig/Pa_ig
gen lx = l.x

* reserves

gen res0 = (Reserve_PPB_ig_lag + Reserve_RC_ig_lag + Reserve_unrealized_lag) / PM_ouv_ig if abs(alpha_ig-1)<.05

* portfolio shares

foreach x in stock bond re loan {
gen l2share_`x' = l2.Asset_`x'_mrkt/l2.Asstot_mrkt
}

keep matricule year res0 lx l2share_*

save "$output/temp", replace



*** contract flows (from enquete revalo)

use "$output/ER", clear
merge m:1 matricule year using "$output/temp", keep(1 3) nogen

* flows

gen flow = log(PM-lag_PM*r)-log(lag_PM)
replace flow = . if abs(flow)>1

* average invested amount

gen avginv = PM/nb_assures*1e6 if nb_assures>1

* size categories

gen avginv10k = avginv<50e3 if avginv<.
gen avginv50k = avginv>=50e3 & avginv<250e3 if avginv<.
gen avginv250k = avginv>=250e3 if avginv<.

* interaction terms

gen Avginv = .
foreach x in 10 50 250 {
	replace Avginv = `x' if avginv`x'k==1
	gen res0_avginv`x'k = res0*avginv`x'k
	gen lx_avginv`x'k = lx*avginv`x'k
	foreach y in stock bond re loan {
		gen l2share_`y'_avginv`x'k = l2share_`y'*avginv`x'k
	}
}

keep if avginv<.

* fixed effects

capture drop matyear
egen matyear = group(matricule year)

* weights

gen temp = (PM+lag_PM)/2
egen totPM = sum(temp), by(year)
gen vw = temp/totPM
drop temp totPM

* labels

label var res0_avginv10k "Lagged reserves x (Avg account value 0--50 k\euro)"
label var res0_avginv50k "Lagged reserves x (Avg account value 50--250 k\euro)"
label var res0_avginv250k "Lagged reserves x (Avg account value 250+ k\euro)"

* regression sample

qui reghdfe flow res0_avginv*50k if open==1 [aw=vw], a(matyear Avginv)
gen sample = e(sample)==1

* regressions

eststo clear

qui eststo: reghdfe flow res0_avginv* if sample==1 [aw=vw], a(matricule year Avginv) vce(cluster matricule year) old

qui eststo: reghdfe flow res0_avginv*50k if sample==1 [aw=vw], a(matyear Avginv) vce(cluster matricule year) old

qui eststo: reghdfe flow l2share_*_avginv* (res0_avginv* = lx_avginv*) if sample==1 [aw=vw], a(matricule year Avginv) cluster(year) old

qui eststo: reghdfe flow l2share_*_avginv* (res0_avginv*50k = lx_avginv*) if sample==1 [aw=vw], a(matyear Avginv) vce(cluster year) old

estout using "$results/sophis.tex", replace style(tex) label cells(b(fmt(%9.2g) star) se(fmt(%9.2g) par)) starlevels(* 0.1 ** 0.05 *** 0.01) mlabel(none) collabel(none) drop(l2share_*_avginv*) varwidth(52)

estout using "$results/sophis_r2.tex", replace style(tex) drop(*) stats(r2_a N, fmt(%8.2g %8.0fc) label("$ R^2$" "Observations")) mlabel(none) collabel(none)

